﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Data;
using System.Web.Services.Description;
using MockPrjTeam2.Common;
using MockPrjTeam2.Entity;

namespace MockPrjTeam2.DAO
{
    public class TrustRegionDao
    {
        private static TrustRegionDao _trustRegionDao;

        public static List<TrustRegion> ToTrustRegionList(DataTable dataTable)
        {
            var reply = new List<TrustRegion>();
            if (dataTable != null && dataTable.Rows.Count > 0)
            {
                foreach (DataRow row in dataTable.Rows)
                {
                    var trustRegion = TrustRegion.MappingEntity(row);
                    if (trustRegion != null)
                    {
                        reply.Add(trustRegion);
                    }
                }
            }

            return reply;
        }

        public static TrustRegionDao Instance
        {
            get
            {
                if (_trustRegionDao == null)
                    _trustRegionDao = new TrustRegionDao();

                return _trustRegionDao;
            }
        }

        public List<TrustRegion> GetAll()
        {
            var reply = new List<TrustRegion>();
            const string query = "Select *,Country.CountryName From TrustRegion inner join Country on TrustRegion.CountryID = Country.CountryID where TrustRegion.IsActive=1 ORDER BY TrustRegionName ASC";
            using (var sqlHelper = new SqlHelper())
            {
                var dtTrustRegion = sqlHelper.ExecuteSelectQuery(query);
                reply = ToTrustRegionList(dtTrustRegion);
            }

            return reply;
        }

        //method GetTrustRegionInActive()
        public DataTable GetInActive()
        {

            string query = "Select TrustRegionName,Description,TrustRegion.IsActive,Country.CountryName,TrustRegionID from TrustRegion inner join Country on  TrustRegion.CountryID=Country.CountryID  order by TrustRegionID asc";
            using (var helper = new SqlHelper())
            {
                return helper.ExecuteSelectQuery(query);
            }
        }
        //method GetTrustRegionById()
         public DataTable GetById(int idTrustRegion)
         {
             string query =
                "select TrustRegionID,TrustRegionName,Description,Country.CountryName,TrustRegion.CountryID from TrustRegion inner join Country on TrustRegion.CountryID=Country.CountryID where TrustRegionID=@Id";

             SqlParameter[] sqlParameters = new SqlParameter[1];
             sqlParameters[0] = new SqlParameter("@Id", SqlDbType.Int);
             sqlParameters[0].Value = idTrustRegion;
             using (var helper = new SqlHelper())
             {
                 return helper.ExecuteSelectQuery(query, sqlParameters);
             }
         }
         //method AddTrustRegion()
         public bool AddTrustRegion(TrustRegion trustRegion)
         {
             int trustRegionId;
             bool reply = false;
             string query = @"insert into TrustRegion(TrustRegionName,Description,CountryID,IsActive) values (@TrustRegionName,@Description,@IsActive,@CountryID)";
             using (var helper = new SqlHelper())
             {
                 SqlParameter[] sqlParameters = new SqlParameter[4];

                 sqlParameters[0] = new SqlParameter("@TrustRegionName", SqlDbType.NVarChar);
                 sqlParameters[0].Value = trustRegion.TrustRegionName;
                 sqlParameters[1] = new SqlParameter("@Description", SqlDbType.NVarChar);
                 sqlParameters[1].Value = trustRegion.Description;
                 sqlParameters[2] = new SqlParameter("@CountryID", SqlDbType.Int);
                 sqlParameters[2].Value = trustRegion.CountryID;
                 sqlParameters[3]=new SqlParameter{ParameterName = "@IsActive",Value = trustRegion.IsActive=true,DbType = DbType.Boolean};
                
                 reply = helper.ExecuteInsertQuery(query, sqlParameters);
             }
             return reply;
         }
         //method UpdateTrustRegion
         public bool UpdateTrustRegion(TrustRegion trustRegion)
         {
             bool reply = false;
             string query =
                 @"update TrustRegion set TrustRegionName=@TrustRegionName,Description=@Description,CountryID=@CountryID where TrustRegion.TrustRegionID=@TrustRegionID ";

             using (var helper = new SqlHelper())
             {
                 SqlParameter[] sqlParameters = new SqlParameter[4];

                 sqlParameters[0] = new SqlParameter("@TrustRegionName", SqlDbType.NVarChar);
                 sqlParameters[0].Value = trustRegion.TrustRegionName;
                 sqlParameters[1] = new SqlParameter("@Description", SqlDbType.NVarChar);
                 sqlParameters[1].Value = trustRegion.Description;
                 sqlParameters[2] = new SqlParameter("@TrustRegionID", SqlDbType.Int);
                 sqlParameters[2].Value = trustRegion.TrustRegionID;
                 sqlParameters[3] = new SqlParameter("@CountryID", SqlDbType.NVarChar);
                 sqlParameters[3].Value = trustRegion.CountryID;
                 
                 reply = helper.ExecuteUpdateQuery(query, sqlParameters);
             }
             return reply;
         }

        //method SetActiveTrustRegion
         public bool SetActive(int trustRegionId, bool status)
         {
             bool reply;
             string query = @"update TrustRegion set IsActive=@IsActive where TrustRegionID=@TrustRegionID ";
             using (var helper = new SqlHelper())
             {
                 SqlParameter[] sqlParameters = new SqlParameter[2];
                 sqlParameters[0] = new SqlParameter("@TrustRegionID", SqlDbType.Int);
                 sqlParameters[0].Value = trustRegionId;
                 sqlParameters[1] = new SqlParameter("@IsActive", SqlDbType.Bit);
                 sqlParameters[1].Value = status;
                 reply = helper.ExecuteUpdateQuery(query, sqlParameters);
             }
             return reply;
         }
         public bool SetInActive(int trustRegionId, bool status)
         {
             bool reply;
             string query = @"update TrustRegion set IsActive=@IsActive where TrustRegionID=@TrustRegionID ";
             using (var helper = new SqlHelper())
             {
                 SqlParameter[] sqlParameters = new SqlParameter[2];
                 sqlParameters[0] = new SqlParameter("@TrustRegionID", SqlDbType.Int);
                 sqlParameters[0].Value = trustRegionId;
                 sqlParameters[1] = new SqlParameter("@IsActive", SqlDbType.Bit);
                 sqlParameters[1].Value = status;
                 reply = helper.ExecuteUpdateQuery(query, sqlParameters);
             }
             return reply;
         }
    
         public DataTable StartWithByNames(string startWith, int flag)
         {
             string query = @"Select TrustRegionID,TrustRegionName,Description,TrustRegion.IsActive,Country.Countryname from TrustRegion inner join Country on  TrustRegion.CountryID=Country.CountryID";
             if (flag == 1)
             {
                 query = startWith.Equals("all") ? query + " order by TrustRegion.TrustRegionName asc" : query +
                     " WHERE  (TrustRegion.TrustRegionName LIKE '['+@TrustRegionName+']%')" +
                     " order by TrustRegion.TrustRegionName asc";
             }
             else
             {
                 query = startWith.Equals("all") ? query +
                     " WHERE TrustRegion.IsActive = 1 order by TrustRegion.TrustRegionName asc" : query +
                     " WHERE  (TrustRegion.TrustRegionName LIKE '['+@TrustRegionName+']%') And TrustRegion.IsActive = 1" +
                     " order by TrustRegion.TrustRegionName asc";
             }
             var sqlParameters = new SqlParameter[1];
             sqlParameters[0] = new SqlParameter("@TrustRegionName", SqlDbType.NVarChar) { Value = startWith };
             using (var helper = new SqlHelper())
             {
                 return helper.ExecuteSelectQuery(query, sqlParameters);
             }
         }
         //method CheckTrustRegionNameExist(
         public int CheckNameExist(string trustRegionName)
         {
             string query = "select count(TrustRegionName) from TrustRegion where TrustRegionName=@trustRegionName";
             using (var helper = new SqlHelper())
             {
                 SqlParameter[] sqlParameters = new SqlParameter[1];
                 sqlParameters[0] = new SqlParameter("@trustRegionName", SqlDbType.NVarChar);
                 sqlParameters[0].Value = trustRegionName;
                 return helper.ExecuteScalar(query, sqlParameters);
             }
         }
    }
}